The spreadsheet template set forth in this article is based on the discussion of formula timing plans in the "Investor workshop" column of the July 1985 AAII Journal. Readers should refer to that article for a more detailed treatment of the topic.
Formula timing plans offer a relatively simple, mechanical alternative to the subjective timing of investments and portfolio composition changes. To implement the formula plans dealt with in the article, the investor must first divide the total portfolio into an aggressive half and a conservative half. Growth stocks with long-term favorable fundamentals, stocks with high beta values, and aggressive mutual funds are usually suggested for the aggressive portfolio, while defensive investments such as bonds and conservative mutual funds typically make up the conservative portfolio. The more negatively correlated the movement of the two portfolios and the more volatile the aggressive portfolio is relative to the conservative portfolio, the more effective the formula plan.
The article described three types of formula plans: constant dollar, constant ratio, and variable ratio.
The constant dollar plan requires the investor to manage the two portfolios over time so as to maintain the original value of the aggressive portfolio. If the aggressive portfolio increases in value, the increase is sold and the proceeds are invested in the conservative portfolio. If the aggressive portfolio decreases in value, the opposite occurs: enough assets are shifted from the conservative portfolio to the aggressive portfolio to restore the latter to its original value. The underlying idea is to induce the investor to buy stocks in the aggressive portfolio when prices are low and sell when prices are high. Care must be taken not to set the target aggressive portfolio value so high that a substantial fall in security prices might force a total depletion of the conservative portfolio.
Under the constant ratio plan, the investor manages the two portfolios so as to maintain a constant ratio of the aggressive portfolio value to the conservative portfolio value (or in other words, to maintain the aggressive portfolio at a constant percentage of the total). Whenever the two portfolios get out of balance, securities are bought and sold between them to restore the original balance. When setting the ratio, the investor should take into account his or her individual risk preference. The higher the proportion of aggressive portfolio value, the greater the overall portfolio risk.
With variable ratio plans, the investor sets several aggressive/conservative ratios, each of them tied to the aggressive portfolio's average security value or to a general market or economic indicator. For instance, if the aggressive portfolio's price rises (falls) a certain number of percentage points above (below) average, a lower (higher) aggressive/ conservative ratio takes effect, and assets are shifted between the two portfolios in accordance with the new ratio. If the price subsequently falls (rises) back to its average level, the original ratio is restored.
All three plans leave unanswered the question of how often the portfolios are to be rebalanced. Too frequent rebalancing will lead to excessive transactions, administrative, and tax-related costs. If the portfolios are rebalanced too infrequently, favorable movements in the aggressive portfolio may be missed. One timing rule is to rebalance when the triggering indicator (aggressive portfolio value, aggressive/conservative ratio, average security price, or general market indicator) deviates a certain percentage from its average, target, or former value. Another timing rule is to rebalance at periodic intervals, say every six months (in order to take advantage of the capital gains tax preferment).
To use the template, enter data in all tinted cells. (Actually, if you are interested in only one type of plan, there is no need to fill in the data in rows 9-16 for the other plans.) Indicate the plan you want to follow in cell A6 (R6C1); in the example, plan #3 has been selected. If you are following the variable ratio plan, cell E15 (R15C5) should be filled with the initial or projected average value of the rebalancing "trigger." This could be the average aggressive portfolio stock price, as in the example, or it could be the projected average value for some general market or economic indicator, such as the S&P 500 index. If you select the average aggressive portfolio stock price--the APprice, cell B31 (R31C2)--as your rebalancing trigger, you could use the spreadsheet to calculate its initial value, then plug this as a permanent value into cell E15. For each security's target percentage of the total portfolio, F25..F28 (R25:28C6) and F40..F41 (R40:41C6), you will probably want to use the initial actual percentages as your subsequent target percentages.
Execute the recalculate command on your spreadsheet, and the indicated portfolio rebalancings are computed and displayed in F9..F16 (R9:16C6) and G9..G16 (R9:16C7). A positive number means to increase the indicated portfolio by that amount, while a negative number signifies a decrease. You can distribute the increase (decrease) any way you want, or you can follow the pattern suggested in column G (7): buy and sell securities in order to bring the actual portfolio distribution back in line with the target distribution. The effect is to take the idea of "buy low, sell high" one step further: sell more of (or buy relatively less of) those securities that have risen more in price, and buy more of (or sell relatively less of) those that have fallen more in price. In the example, the rebalancing "trigger" (the APprice, $25.26) has risen more than 20% above average ($20.00), and a transfer of $5,400 from the aggressive portfolio to the conservative portfolio is indicated.
You can use the template over time to guide your investment actions, or you can use it to play "whatif": for instance, to test which formula plan would perform the best in a generally. rising (or falling or meandering) market; or to test the effect of changing various targets (the targeted AP value, percentage price deviation, etc.) under varying market environments. You can also expand the template by, for example, incorporating more and narrower price zones into the variable ratio plan. The template is not cast in stone, and you are encouraged to mold it to match your own ideas and needs.
